﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for cls_tb_BAOCAOTHUPHITHEONGANH
/// </summary>
public class cls_tb_BAOCAOTHUPHITHEONGANH
{
	public cls_tb_BAOCAOTHUPHITHEONGANH()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static DataTable DONGPHI_BaoCaoThuPhiTheoChuyenNganh(string F_MACHN, string F_MACN, DateTime F_TUNGAY, DateTime F_DENNGAY)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MACHN, F_MACN, F_TUNGAY, F_DENNGAY };
        string[] parameters = { "@F_MACHN", "@F_MACN", "@F_TUNGAY", "@F_DENNGAY" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("DONGPHI_BaoCaoThuPhiTheoChuyenNganh", para);
        }
        catch
        { }
        return dt;
    }
    public static DataTable DONGPHI_BaoCaoThuPhiTheoChuyenNganh_GetF_MACN_DISTINCT(string F_MACHN, string F_MACN, DateTime F_TUNGAY, DateTime F_DENNGAY)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MACHN, F_MACN, F_TUNGAY, F_DENNGAY };
        string[] parameters = { "@F_MACHN", "@F_MACN", "@F_TUNGAY", "@F_DENNGAY" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("DONGPHI_BaoCaoThuPhiTheoChuyenNganh_GetF_MACN_DISTINCT", para);
        }
        catch
        { }
        return dt;
    }
    //DONGPHI_BaoCaoHocVienRutPhi
    public static DataTable DONGPHI_BaoCaoHocVienRutPhi(string F_MACHN, string F_MACN, DateTime F_TUNGAY, DateTime F_DENNGAY)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MACHN, F_MACN, F_TUNGAY, F_DENNGAY };
        string[] parameters = { "@F_MACHN", "@F_MACN", "@F_TUNGAY", "@F_DENNGAY" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("DONGPHI_BaoCaoHocVienRutPhi", para);
        }
        catch
        { }
        return dt;
    }
    //DONGPHI_BaoCaoThuPhiTheoLop
    public static DataTable DONGPHI_BaoCaoThuPhiTheoLop(string F_MADT)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MADT };
        string[] parameters = { "@F_MADT" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("DONGPHI_BaoCaoThuPhiTheoLop", para);
        }
        catch
        { }
        return dt;
    }
    //DONGPHI_SumBaoCaoThuPhiTheoChuyenNganh
    //------------
    public static DataTable DONGPHI_SumBaoCaoThuPhiTheoChuyenNganh(string F_MACHN, string F_MACN, DateTime F_TUNGAY, DateTime F_DENNGAY)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MACHN, F_MACN, F_TUNGAY, F_DENNGAY };
        string[] parameters = { "@F_MACHN", "@F_MACN", "@F_TUNGAY", "@F_DENNGAY" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("DONGPHI_SumBaoCaoThuPhiTheoChuyenNganh", para);
        }
        catch
        { }
        return dt;
    }
    
    public static DataTable GetByMABCTPTN(Guid MABCTPTN)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN };
        string[] parameters = { "@MABCTPTN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("tb_BAOCAOTHUPHITHEONGANH_GetByMABCTPTN", para);
        }
        catch
        { }
        return dt;
    }
    public static DataTable CHITIET_GetByMABCTPTN(Guid MABCTPTN)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN };
        string[] parameters = { "@MABCTPTN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("tb_BAOCAOTHUPHITHEONGANH_CHITIET_GetByMABCTPTN", para);
        }
        catch
        { }
        return dt;
    }
    public static DataTable CHITIET_GetByMABCTPTN_F_MACN(Guid MABCTPTN, string F_MACN)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN, F_MACN };
        string[] parameters = { "@MABCTPTN", "@F_MACN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("tb_BAOCAOTHUPHITHEONGANH_CHITIET_GetByMABCTPTN_F_MACN", para);
        }
        catch
        { }
        return dt;
    }
    //_GetF_MACN_DISTINCT
    public static DataTable CHITIET_GetByMABCTPTN_GetF_MACN_DISTINCT(Guid MABCTPTN)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN };
        string[] parameters = { "@MABCTPTN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("tb_BAOCAOTHUPHITHEONGANH_CHITIET_GetByMABCTPTN_GetF_MACN_DISTINCT", para);
        }
        catch
        { }
        return dt;
    }
    public static DataTable CHITIET_Get_SUM_ByMABCTPTN(Guid MABCTPTN)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN };
        string[] parameters = { "@MABCTPTN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("tb_BAOCAOTHUPHITHEONGANH_CHITIET_Get_SUM_ByMABCTPTN", para);
        }
        catch
        { }
        return dt;
    }
    public static DataTable GetByF_MACN(string F_MACN)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MACN };
        string[] parameters = { "@F_MACN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("tb_BAOCAOTHUPHITHEONGANH_GetByF_MACN", para);
        }
        catch
        { }
        return dt;
    }
    public static DataTable GetByF_MACNF_DADUYET(string F_MACN, int F_DADUYET)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MACN, F_DADUYET };
        string[] parameters = { "@F_MACN", "@F_DADUYET" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("tb_BAOCAOTHUPHITHEONGANH_GetByF_MACNF_DADUYET", para);
        }
        catch
        { }
        return dt;
    }
    public static DataTable GetWithNgayDuyetCuoiByF_MACN(string F_MACN)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MACN };
        string[] parameters = { "@F_MACN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("tb_BAOCAOTHUPHITHEONGANH_GetWithNgayDuyetCuoiByF_MACN", para);
        }
        catch
        { }
        return dt;
    }
    public static DateTime GetNgayDuyetCuoiByF_MACN(string F_MACN)
    {
        DateTime rs = new DateTime(2013, 11, 15, 0, 0, 0);
        //chon ngay 15/11/2013 lam ngay khoa neu truoc do chua khoa

        DataTable dt = new DataTable();
        dt = GetWithNgayDuyetCuoiByF_MACN(F_MACN);
        if (dt.Rows.Count > 0)
        {
            rs = DateTime.Parse(dt.Rows[0]["F_NGAYDUYET"].ToString());
        }
        return rs;
    }
    public static DataTable GetWithF_DENNGAY_LAST_ByF_MACN(string F_MACN)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MACN };
        string[] parameters = { "@F_MACN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            dt = connect.GetSelectCommand("tb_BAOCAOTHUPHITHEONGANH_GetWithF_DENNGAY_LAST_ByF_MACN", para);
        }
        catch
        { }
        return dt;
    }
    public static DateTime GetF_DENNGAY_LAST_ByF_MACN(string F_MACN)
    {
        DateTime rs = new DateTime(2013, 11, 15, 0, 0, 0);
        //chon ngay 15/11/2013 lam ngay khoa neu truoc do chua khoa

        DataTable dt = new DataTable();
        dt = GetWithF_DENNGAY_LAST_ByF_MACN(F_MACN);
        if (dt.Rows.Count > 0)
        {
            rs = DateTime.Parse(dt.Rows[0]["F_DENNGAY"].ToString());
        }
        return rs;
    }
    public static string GetF_MACHNByMABCTPTN(Guid MABCTPTN)
    {
        string rs = "";
        DataTable dt = new DataTable();
        dt = GetByMABCTPTN(MABCTPTN);
        if (dt.Rows.Count > 0)
        {
            rs = dt.Rows[0]["F_MACHN"].ToString();
        }
        return rs;
    }
    public static string GetF_MACNByMABCTPTN(Guid MABCTPTN)
    {
        string rs = "";
        DataTable dt = new DataTable();
        dt = GetByMABCTPTN(MABCTPTN);
        if (dt.Rows.Count > 0)
        {
            rs = dt.Rows[0]["F_MACN"].ToString();
        }
        return rs;
    }
    public static int GetF_NGUOILAPBIEUByMABCTPTN(Guid MABCTPTN)
    {
        int rs = 0;
        DataTable dt = new DataTable();
        dt = GetByMABCTPTN(MABCTPTN);
        if (dt.Rows.Count > 0)
        {
            rs = int.Parse(dt.Rows[0]["F_NGUOILAPBIEU"].ToString());
        }
        return rs;
    }
    public static decimal GetF_TONGHOCBONGByMABCTPTN(Guid MABCTPTN)
    {
        decimal rs = 0;
        DataTable dt = new DataTable();
        dt = GetByMABCTPTN(MABCTPTN);
        if (dt.Rows.Count > 0)
        {
            rs = decimal.Parse(dt.Rows[0]["F_TONGHOCBONG"].ToString());
        }
        return rs;
    }
    public static decimal GetF_TONGTIENPHATByMABCTPTN(Guid MABCTPTN)
    {
        decimal rs = 0;
        DataTable dt = new DataTable();
        dt = GetByMABCTPTN(MABCTPTN);
        if (dt.Rows.Count > 0)
        {
            rs = decimal.Parse(dt.Rows[0]["F_TONGTIENPHAT"].ToString());
        }
        return rs;
    }
    public static decimal GetF_TONGTHUCTHUByMABCTPTN(Guid MABCTPTN)
    {
        decimal rs = 0;
        DataTable dt = new DataTable();
        dt = GetByMABCTPTN(MABCTPTN);
        if (dt.Rows.Count > 0)
        {
            rs = decimal.Parse(dt.Rows[0]["F_TONGTHUCTHU"].ToString());
        }
        return rs;
    }
    public static DateTime GetF_TUNGAYByMABCTPTN(Guid MABCTPTN)
    {
        DateTime rs = DateTime.Now;
        DataTable dt = new DataTable();
        dt = GetByMABCTPTN(MABCTPTN);
        if (dt.Rows.Count > 0)
        {
            rs = DateTime.Parse(dt.Rows[0]["F_TUNGAY"].ToString());
        }
        return rs;
    }
    public static DateTime GetF_DENNGAYByMABCTPTN(Guid MABCTPTN)
    {
        DateTime rs = DateTime.Now;
        DataTable dt = new DataTable();
        dt = GetByMABCTPTN(MABCTPTN);
        if (dt.Rows.Count > 0)
        {
            rs = DateTime.Parse(dt.Rows[0]["F_DENNGAY"].ToString());
        }
        return rs;
    }
    public static DateTime GetF_NGAYLAPByMABCTPTN(Guid MABCTPTN)
    {
        DateTime rs = DateTime.Now;
        DataTable dt = new DataTable();
        dt = GetByMABCTPTN(MABCTPTN);
        if (dt.Rows.Count > 0)
        {
            rs = DateTime.Parse(dt.Rows[0]["F_NGAYLAP"].ToString());
        }
        return rs;
    }
    public static string Insert(Guid MABCTPTN, string F_MACHN, DateTime F_TUNGAY, DateTime F_DENNGAY, string F_MACN, DateTime F_NGAYLAP, decimal F_TONGHOCBONG, decimal F_TONGTIENPHAT, decimal F_TONGTHUCTHU, string F_BANGIAMDOC, string F_CANBOQUANLYCHINHANH, int F_NGUOILAPBIEU, int F_CANBOXETDUYET, DateTime F_NGAYDUYET, int F_DADUYET)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN, F_MACHN, F_TUNGAY, F_DENNGAY, F_MACN, F_NGAYLAP, F_TONGHOCBONG, F_TONGTIENPHAT, F_TONGTHUCTHU, F_BANGIAMDOC, F_CANBOQUANLYCHINHANH, F_NGUOILAPBIEU, F_CANBOXETDUYET, F_NGAYDUYET, F_DADUYET };
        string[] parameters = { "@MABCTPTN", "@F_MACHN", "@F_TUNGAY", "@F_DENNGAY", "@F_MACN", "@F_NGAYLAP", "@F_TONGHOCBONG", "@F_TONGTIENPHAT", "@F_TONGTHUCTHU", "@F_BANGIAMDOC", "@F_CANBOQUANLYCHINHANH", "@F_NGUOILAPBIEU", "@F_CANBOXETDUYET", "@F_NGAYDUYET", "@F_DADUYET" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }
        try
        {
            connect.ExecuteQuery("tb_BAOCAOTHUPHITHEONGANH_Insert", para);
            return "OK";
        }
        catch
        {
            return "ERR";
        }
    }
    public static string Duyet(Guid MABCTPTN, int F_CANBOXETDUYET)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN, F_CANBOXETDUYET };
        string[] parameters = { "@MABCTPTN", "@F_CANBOXETDUYET" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }
        try
        {
            connect.ExecuteQuery("tb_BAOCAOTHUPHITHEONGANH_Duyet", para);
            return "OK";
        }
        catch(SqlException ex)
        {
            return ex.Message;
        }
    }
    public static string Chitiet_Insert(Guid MABCTPTN, string F_MALOP, int F_SISO, int F_SOLUOTDONG, decimal F_TIENHOCBONG, decimal F_TIENPHAT, decimal F_TIENTHUCTHU)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN, F_MALOP, F_SISO, F_SOLUOTDONG, F_TIENHOCBONG, F_TIENPHAT, F_TIENTHUCTHU };
        string[] parameters = { "@MABCTPTN", "@F_MALOP", "@F_SISO", "@F_SOLUOTDONG", "@F_TIENHOCBONG", "@F_TIENPHAT", "@F_TIENTHUCTHU" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }
        try
        {
            connect.ExecuteQuery("tb_BAOCAOTHUPHITHEONGANH_CHITIET_Insert", para);
            return "OK";
        }
        catch
        {
            return "ERR";
        }
    }
    public static string Chitiet_Delete(Guid MABCTPTN)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN };
        string[] parameters = { "@MABCTPTN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }
        try
        {
            connect.ExecuteQuery("tb_BAOCAOTHUPHITHEONGANH_CHITIET_Delete", para);
            return "OK";
        }
        catch
        {
            return "ERR";
        }
    }
    public static string Delete(Guid MABCTPTN)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { MABCTPTN };
        string[] parameters = { "@MABCTPTN" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }
        try
        {
            connect.ExecuteQuery("tb_BAOCAOTHUPHITHEONGANH_Delete", para);
            return "OK";
        }
        catch(Exception ex)
        {
            return ex.Message;
        }
    }
}